/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.p10.miage.gestionnote.modelservice;

import fr.p10.miage.gestionnote.model.Ec;
import fr.p10.miage.gestionnote.model.Note;
import fr.p10.miage.gestionnote.model.Note;
import static com.sun.corba.se.spi.presentation.rmi.StubAdapter.request;
import fr.p10.miage.gestionabsence.model.Connection;
import fr.p10.miage.gestionabsence.model.SessionUtilisateur;
import fr.p10.miage.gestionabsence.model.Utilisateur;
import fr.p10.miage.gestionnote.model.Controle;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import org.apache.struts.chain.contexts.ServletActionContext;

/**
 *
 * @author mehdibeggas
 */
public class ControleServices extends UtilService {

    private static Connection cx;

    public static List<Note> getNotes(int idcontrole,int idEtudiant) throws SQLException 
    {
        
        List<Note> listeNotes = new ArrayList<Note>();

        String sql = "SELECT *\n"
                + "FROM note n\n"
                + "WHERE n.idcontrole = " + idcontrole + "\n";
            sql += "AND n.idetudiant =" + /*ControleServices.getIdEtudiant()*/idEtudiant + "";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            while (resultat.next()) {
                listeNotes.add(new Note(
                        resultat.getFloat("note"),
                        resultat.getBoolean("dispense")));
            }
            resultat.close();
        }

        pst.close();
        return listeNotes;
    }
    
    public static List<Note> getNotes(int idcontrole) throws SQLException {

        //Utilisateur utilisateurTmp = (Utilisateur) SessionUtilisateur.getUtilisateur(ControleServices.getRequest());
        
        List<Note> listeNotes = new ArrayList<Note>();

        String sql = "SELECT *\n"
                + "FROM note n\n"
                + "WHERE n.idcontrole = " + idcontrole + "\n";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            while (resultat.next()) {
                listeNotes.add(new Note(
                        resultat.getFloat("note"),
                        resultat.getBoolean("dispense")));
            }
            resultat.close();
        }

        pst.close();
        return listeNotes;
    }
    public static float calculNoteMoyenneControle(int idcontrole) throws SQLException, Exception {
        float moy = 0;
        String sql = "SELECT ROUND(AVG(n.note),2) AS note\n"
                + "FROM note n\n"
                + "WHERE n.idcontrole = " + idcontrole + "\n"
                + "AND n.dispense=0";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                    moy = resultat.getFloat("note");
            } else {
                throw new Exception("Pas un float retournée");
            }
            resultat.close();
        }
        pst.close();
        return moy;
    }

    public static float calculNoteBasseControle(int idcontrole) throws SQLException, Exception {
        float moy = 0;
        String sql = "SELECT ROUND(MIN(n.note),2) AS note\n"
                + "FROM note n\n"
                + "WHERE n.idcontrole = " + idcontrole + "\n"
                + "AND n.dispense=0";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                    moy = resultat.getFloat("note");
            } else {
                throw new Exception("Pas un float retournée");
            }
            resultat.close();
        }
        pst.close();
        return moy;
    }

    public static float calculNoteHauteControle(int idcontrole) throws SQLException, Exception {
        float moy = 0;
        String sql = "SELECT ROUND(MAX(n.note),2) AS note\n"
                + "FROM note n\n"
                + "WHERE n.idcontrole = " + idcontrole + "\n"
                + "AND n.dispense=0";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                if (resultat.getFloat("note") != 0) {
                    moy = resultat.getFloat("note");
                }
            } else {
                throw new Exception("Pas un float retournée");
            }
            resultat.close();
        }
        pst.close();
        return moy;
    }

    public static Controle getControleById() throws SQLException 
    {
        return null;
    }

    public static Controle getControleByIdUser() {
        return null;
    }

    public static int ajouterControle(String label, float coefficient,int idec,int type,int echelle,int idp) throws SQLException, Exception 
    {
        int idControle=0;
        String generatedColumns[] = { "IDCONTROLE" };
        //label=new String(label.getBytes("ISO-8859-15"), "UTF8");
           try {
            String insertTableSQL = "INSERT INTO CONTROLE (label,coefficient,idec,type,echelle,idp,idcontrole) VALUES(?,?,?,?,?,?,s_controle_t1.nextval)";
            PreparedStatement preparedStatement = cx.getBDD().prepareStatement(insertTableSQL,generatedColumns);
            preparedStatement.setString(1, label);
            preparedStatement.setFloat(2, coefficient);
            preparedStatement.setInt(3, idec);
            preparedStatement.setInt(4, type);
            preparedStatement.setInt(5, echelle);
            preparedStatement.setInt(6, idp);
            preparedStatement.executeUpdate();
            
            ResultSet rs= preparedStatement.getGeneratedKeys();

            if (rs != null && rs.next()) 
            {
                idControle=rs.getInt(1);
            }
            else
            {
                rs.close();
            }

            preparedStatement.close();
            
        } catch (SQLException s) {
            System.out.println("SQL statement add controle is not executed!"+s);
        }
           return idControle;
    }

    public static void ajouterNoteEtudiant(int idControle, int idEtudiant,float note,boolean dispense) {
        try {
            String insertTableSQL = "INSERT INTO NOTE\n"
                    + "(idcontrole,idetudiant,note,dispense) VALUES\n"
                    + "(?,?,?,?)";
            PreparedStatement preparedStatement = cx.getBDD().prepareStatement(insertTableSQL);
            preparedStatement.setInt(1, idControle);
            preparedStatement.setInt(2, idEtudiant);
            preparedStatement.setFloat(3, note);
            preparedStatement.setBoolean(4, dispense);
            preparedStatement.executeUpdate();


            preparedStatement.close();
        } catch (SQLException s) {
            System.out.println("SQL statement add etudiant is not executed!"+s);
        }
    }

    public static int modifierControle(String label, float coefficient,int idec,int type,int echelle,int idp,int idcontrole) throws SQLException, Exception 
    {
        int idControle=0;
           //label=new String(label.getBytes("ISO-8859-15"), "UTF8");
           try {
            String insertTableSQL = "UPDATE CONTROLE SET label=?,coefficient=?,type = ?,echelle =? WHERE idec=? AND idcontrole=? AND idp=?";
            PreparedStatement preparedStatement = cx.getBDD().prepareStatement(insertTableSQL);
            preparedStatement.setString(1, label);
            preparedStatement.setFloat(2, coefficient);
            preparedStatement.setInt(3, type);
            preparedStatement.setInt(4, echelle);
            preparedStatement.setInt(5, idec);
            preparedStatement.setInt(6, idcontrole);
            preparedStatement.setInt(7, idp);
            preparedStatement.executeUpdate();
            
        } catch (SQLException s) {
            System.out.println("SQL statement update controle is not executed!"+s);
        }
           return idControle;
    }
    public static void modifierNoteEtudiant(int idControle, int idEtudiant,float note,boolean dispense) throws SQLException, Exception {
         int count = 0;
        String sql = "SELECT count(n.note) as result\n" +
                    "FROM note n\n" +
                    "WHERE n.idetudiant = "+idEtudiant+"\n" +
                    "AND n.idcontrole = "+idControle+"";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                if (resultat.getInt("result") != 0) {
                    count = resultat.getInt("result");
                }
            } else {
                count=0;
            }
            resultat.close();
        }
        pst.close();
        
        if(count==1)
        {
            String insertTableSQL = "UPDATE NOTE SET note=?,dispense=? WHERE idcontrole=? AND idetudiant=?";
            PreparedStatement preparedStatement = cx.getBDD().prepareStatement(insertTableSQL);
            preparedStatement.setFloat(1,note);
            preparedStatement.setBoolean(2,dispense);
            preparedStatement.setInt(3,idControle);
            preparedStatement.setInt(4,idEtudiant);
            preparedStatement.executeUpdate();
            preparedStatement.close();


            preparedStatement.close();
        }
        else
        {
            try {
                String insertTableSQL = "INSERT INTO NOTE\n"
                        + "(idcontrole,idetudiant,note,dispense) VALUES\n"
                        + "(?,?,?,?)";
                PreparedStatement preparedStatement = cx.getBDD().prepareStatement(insertTableSQL);
                preparedStatement.setInt(1, idControle);
                preparedStatement.setInt(2, idEtudiant);
                preparedStatement.setFloat(3, note);
                preparedStatement.setBoolean(4, dispense);
                preparedStatement.executeUpdate();


                preparedStatement.close();
            } catch (SQLException s) {
                System.out.println("SQL statement add etudiant is not executed!"+s);
            }
        }
    }
    public static Note getNoteByEtudiantId(int idControle, int idEtudiant) throws SQLException, Exception {
        Note moy = null;
        String sql = "SELECT ROUND(n.note,2) AS note, n.dispense\n"
                + "FROM note n\n"
                + "WHERE n.idcontrole = " + idControle + "\n"
                + "AND n.idetudiant = " + idEtudiant + "";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                try{
                    moy = new Note(resultat.getFloat("note"),resultat.getBoolean("dispense"));
                } catch (SQLException ex) {
                    throw new Exception("Note etudiant 2 pas un float retournée"+ex);
                }
            } else {
                moy=null;
            }
            resultat.close();
        }
        pst.close();
        return moy;
    }

    public static int calculNbEtudiantNote(int idControle) throws SQLException, Exception {
        int nbEtudiant = 0;
        String sql = "SELECT Count(*) as result\n"
                + "FROM note n\n"
                + "WHERE n.idcontrole = " + idControle + "\n"
                + "AND n.dispense=0";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                if (resultat.getInt("result") != 0) {
                    nbEtudiant = resultat.getInt("result");
                }
            } else {
                throw new Exception("Pas un int retourné");
            }
            resultat.close();
        }
        pst.close();
        return nbEtudiant;

    }

    public static int calculNbEtudiantDispense(int idControle) throws SQLException, Exception {
        int nbEtudiant = 0;
        String sql = "SELECT Count(*) as result\n"
                + "FROM note n\n"
                + "WHERE n.idcontrole = " + idControle + "\n"
                + "AND n.dispense=1";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                if (resultat.getInt("result") != 0) {
                    nbEtudiant = resultat.getInt("result");
                }
            } else {
                throw new Exception("Pas un int retourné");
            }
            resultat.close();
        }
        pst.close();
        return nbEtudiant;

    }

    /*
     * Deprecier je ne sais pas à quoi correspond p.nbrgrp, mais notre systeme ne l'utilise pas
     */
    public static int calculNbEtudiantClasse(int idControle) throws SQLException, Exception {
        int nbEtudiant = 0;

        String sql = "SELECT p.nbrgrp as result\n"
                + "FROM controle C, Promotion P\n"
                + "WHERE C.idp=P.idp\n"
                + "AND C.idcontrole=" + idControle + "";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                if (resultat.getInt("result") != 0) {
                    nbEtudiant = resultat.getInt("result");
                }
            } else {
                throw new Exception("Pas un int retourné");
            }
            resultat.close();
        }
        pst.close();
        return nbEtudiant;

    }

    public static void publierNote(int idec, int idControle) throws SQLException, Exception {
        try {
            int res;
            Statement st = cx.getBDD().createStatement();
            String requete = "UPDATE controle SET publier=1 WHERE idcontrole=" + idControle + " AND idec=" + idec + "";

            res = st.executeUpdate(requete);
            st.close();

        } catch (SQLException ex) {
            System.err.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
    }
}
